<?php 
	class WS_PersonalInformationMod {
		
		private $_DB;
		
		public function __construct() {

			$this->_DB 	= new ConfigDB();

		}
		
		public function insertPersonalInformation($strArray) {

			$strID 			= escapestring($strID);
			$account_no 	= escapestring($strArray['account_no']);
			$first_name 	= escapestring($strArray['first_name']);
			$last_name 		= escapestring($strArray['last_name']);
			$middle_name 	= escapestring($strArray['middle_name']);
			$birthdate 		= escapestring($strArray['birthdate']);
			$gender 		= escapestring($strArray['gender']);
			$email 			= escapestring($strArray['email']);
			$contact_no 	= escapestring($strArray['contact_no']);
			$address 		= escapestring($strArray['address']);
			$guardian 		= escapestring($strArray['guardian']);
			
			$access 		= escapestring($strArray['access']);
			$dept_id 		= escapestring($strArray['dept_id']);
			
			$log_user 		= escapestring($strArray['log_user']);
			$log_pass 		= escapestring($strArray['log_pass']);
			
			$this->_DB->queryDB("INSERT INTO `tbl_users_account` (`account_no`,
																  `first_name`,
																  `last_name`,
																  `middle_name`,
																  `birthdate`,
																  `gender`,
																  `email`,
																  `contact_no`,
																  `address`,
																  `guardian`,
																  `account_type`,
																  `dept_idFK`,
																  `log_user`,
																  `log_pass`) VALUES ('" . $account_no 		. "',
																					  '" . $first_name 		. "',
																					  '" . $last_name 		. "',
																					  '" . $middle_name 	. "',
																					  '" . $birthdate 		. "',
																					  '" . $gender 			. "',
																					  '" . $email 			. "',
																					  '" . $contact_no 		. "',
																					  '" . $address 		. "',
																					  '" . $guardian 		. "',
																					  '" . $access 			. "',
																					  '" . $dept_id			. "',
																					  '" . $log_user 		. "',
																					  '" . md5($log_pass)	. "')");
			return mysql_insert_id();
			
		}
		
		public function updatePersonalInformation($strArray, $strID) {

			$whereCondition = '';
			
			$strID 			= escapestring($strID);
			$first_name 	= escapestring($strArray['first_name']);
			$last_name 		= escapestring($strArray['last_name']);
			$middle_name 	= escapestring($strArray['middle_name']);
			$birthdate 		= escapestring($strArray['birthdate']);
			$gender 		= escapestring($strArray['gender']);
			$email 			= escapestring($strArray['email']);
			$contact_no 	= escapestring($strArray['contact_no']);
			$address 		= escapestring($strArray['address']);
			$guardian 		= escapestring($strArray['guardian']);
			
			$access 		= escapestring($strArray['access']);
			$dept_id 		= escapestring($strArray['dept_id']);
			$account_no 	= escapestring($strArray['account_no']);
			
			$log_user 		= escapestring($strArray['log_user']);
			
			$log_pass 		= escapestring($strArray['log_pass']);
			
			if ( !empty($access) ) {
				$whereCondition .= "`account_type` = '" . $access 	. "', ";
			}
			
			if ( !empty($account_no) ) {
				$whereCondition .= "`account_no` = '" . $account_no 	. "', ";
			}
			
			if ( !empty($log_user) ) {
				$whereCondition .= "`log_user` = '" . $log_user	. "', ";
			}
			
			if ( !empty($log_pass) ) {
				$whereCondition .= "`log_pass` = '" . md5($log_pass)	. "', ";
			}

			$this->_DB->queryDB("UPDATE `tbl_users_account` SET `first_name` 	= '" . $first_name	. "',
																`last_name` 	= '" . $last_name 	. "',
																`middle_name` 	= '" . $middle_name . "',
																`birthdate` 	= '" . $birthdate 	. "',
																`gender` 		= '" . $gender 		. "',
																`email` 		= '" . $email 		. "',
																`contact_no` 	= '" . $contact_no 	. "',
																`address` 		= '" . $address 	. "',
																`guardian` 		= '" . $guardian 	. "',
																`dept_idFK`		= '" . $dept_id		. "',
																" . $whereCondition . "
																`first_reg` 	= 'N'
															  WHERE user_idPK 	= '" . $strID 		. "'");

		}
		
		public function deletePersonalInformation($strID) {

			$strID 			= escapestring($strID);
			
			$this->_DB->queryDB("UPDATE `tbl_users_account` SET `is_deleted` = 'Y' WHERE user_idPK 	= '" . $strID . "'");
			
		}
		
		
		public function updateStudentPersonalInformation($strArray, $strID) {

			$whereCondition = '';
			
			$strID 			= escapestring($strID);
			$first_name 	= escapestring($strArray['first_name']);
			$last_name 		= escapestring($strArray['last_name']);
			$middle_name 	= escapestring($strArray['middle_name']);
			$birthdate 		= escapestring($strArray['birthdate']);
			$gender 		= escapestring($strArray['gender']);
			$email 			= escapestring($strArray['email']);
			$contact_no 	= escapestring($strArray['contact_no']);
			$address 		= escapestring($strArray['address']);
			$guardian 		= escapestring($strArray['guardian']);

			$this->_DB->queryDB("UPDATE `tbl_students` SET `first_name` 	= '" . $first_name	. "',
															`last_name` 	= '" . $last_name 	. "',
															`middle_name` 	= '" . $middle_name . "',
															`birthdate` 	= '" . $birthdate 	. "',
															`gender` 		= '" . $gender 		. "',
															`email` 		= '" . $email 		. "',
															`contact_no` 	= '" . $contact_no 	. "',
															`address` 		= '" . $address 	. "',
															`guardian` 		= '" . $guardian 	. "',
															`first_reg` 	= 'N'
														  WHERE `stud_idPK` 	= '" . $strID 	. "'");

		}
		
		public function getPersonalInformation($strID) {

			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT `account_no`,
												 `account_type`,
												 `dept_idFK`,
												 `picture`,
												 `first_name`,
												 `last_name`,
												 `middle_name`,
												 `birthdate`,
												 `gender`,
												 `email`,
												 `contact_no`,
												 `address`,
												 `guardian`,
												 `log_user`
											FROM `tbl_users_account` WHERE `user_idPK` 	= '" . $strID . "' LIMIT 1");

		}
		
		public function accountNumber($strID) {

			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT COUNT(*) AS `count` FROM `tbl_users_account` WHERE `account_no` = '" . $strID . "'");

		}
		
		public function accountUserPass($strUser, $strPass) {

			$strUser 	= escapestring($strUser);
			$strPass 	= escapestring($strPass);

			return $this->_DB->selectDB2("SELECT COUNT(*) AS `count` FROM `tbl_users_account` WHERE `log_user` = '" . $strUser . "' AND `log_pass` = '" . md5($strPass) . "'");

		}
		
		public function getStudentPersonalInformation($strID) {

			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT  `stud_idPK`,
												  `stud_no`,
												  `log_user`,
												  `log_pass`,
												  `picture`,
												  `first_name`,
												  `last_name`,
												  `middle_name`,
												  `birthdate`,
												  `gender`,
												  `email`,
												  `contact_no`,
												  `address`,
												  `guardian`,
												  `course_idFK`
												FROM `tbl_students` WHERE `stud_idPK` = '" . $strID . "' AND `is_deleted` = 'N' and `is_active` = 'Y' LIMIT 1");

		}
		
		public function listPersonalInformation($strSearch, $pageLimit, $strAccountType, $strDepartment) {
			
			$strSearch 		= escapestring($strSearch);
			$whereCondition = '';
			
			if ( !empty($strSearch) ) {
				$whereCondition .= " AND (`first_name` 	LIKE '%" . $strSearch . "%'
									  OR  `last_name` 	LIKE '%" . $strSearch . "%'
									  OR  `email` 		LIKE '%" . $strSearch . "%'
									  OR  `account_no` 	= '" . $strSearch . "') ";
			}
			
			if (!empty($strDepartment)) {
				$strDepartment 	 = escapestring($strDepartment);
				$whereCondition .= " AND `dept_idFK` 	= '" . $strDepartment . "' ";
			}

			return $this->_DB->selectDB("SELECT `user_idPK`,
												`account_no`,
												`log_user`,
												`picture`,
												`first_name`,
												`last_name`,
												`middle_name`,
												`birthdate`,
												`gender`,
												`email`,
												`contact_no`,
												`address`, 
												`guardian`,
												`first_reg`,
												`account_type`,
												`dept_idFK`,
												`is_deleted`
											FROM `tbl_users_account` WHERE `account_type` IN (" . $strAccountType . ") AND `is_deleted` = 'N'" . $whereCondition . $pageLimit);

		}
		
		public function getOldPassword($strPass) {

			$strPass 	= escapestring($strPass);

			return $this->_DB->selectDB2("SELECT COUNT(*) as count_record FROM `tbl_users_account` WHERE `log_pass` = '" . md5($strPass) . "' LIMIT 1");
			
		}
		
		public function getStudentOldPassword($strPass) {

			$strPass 	= escapestring($strPass);

			return $this->_DB->selectDB2("SELECT COUNT(*) as count_record FROM `tbl_students` WHERE `log_pass` = '" . md5($strPass) . "' LIMIT 1");
			
		}
		
		public function updatePassword($strArray, $strID) {

			$strUsername = escapestring($strArray['username']);
			$strPassword = escapestring($strArray['password']);
			$strID 		 = escapestring($strID);

			return $this->_DB->queryDB("UPDATE `tbl_users_account` SET `log_user` = '" . $strUsername . "', `log_pass` = '" . md5($strPassword) . "' WHERE user_idPK = '" . $strID . "'");
			
		}
		
		public function updateStudentPassword($strArray, $strID) {

			$strUsername = escapestring($strArray['username']);
			$strPassword = escapestring($strArray['password']);
			$strID 		 = escapestring($strID);

			return $this->_DB->queryDB("UPDATE `tbl_students` SET `log_user` = '" . $strUsername . "', `log_pass` = '" . md5($strPassword) . "' WHERE stud_idPK = '" . $strID . "'");
			
		}
		
		public function getUsername($strID) {
			
			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT `log_user` FROM `tbl_users_account` WHERE `user_idPK` = '" . $strID . "' LIMIT 1");
			
		}
		
		public function getStudentUsername($strID) {
			
			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT `log_user` FROM `tbl_students` WHERE `stud_idPK` = '" . $strID . "' LIMIT 1");
			
		}
		
		public function getPicture($strID) {
			
			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT `picture` FROM `tbl_users_account` WHERE user_idPK = '" . $strID . "' LIMIT 1");
			
		}
		
		public function getStudentPicture($strID) {
			
			$strID 	= escapestring($strID);

			return $this->_DB->selectDB2("SELECT `picture` FROM `tbl_students` WHERE stud_idPK = '" . $strID . "' LIMIT 1");
			
		}
		
		
		public function changePicture($strFile, $strID) {
			
			$strFile 	 = escapestring($strFile);
			$strID 		 = escapestring($strID);

			return $this->_DB->queryDB("UPDATE `tbl_users_account` SET `picture` = '" . $strFile . "' WHERE user_idPK = '" . $strID . "'");
			
		}
		
		public function changeStudentPicture($strFile, $strID) {
			
			$strFile 	 = escapestring($strFile);
			$strID 		 = escapestring($strID);

			return $this->_DB->queryDB("UPDATE `tbl_students` SET `picture` = '" . $strFile . "' WHERE stud_idPK = '" . $strID . "'");
			
		}
		
	}
?>